﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace Common
{

    /// ExcelHelper 帮助类
    /// <summary>
    /// ExcelHelper 帮助类
    /// </summary>
    public class ExcelHelper
    {
        readonly DataSet _ds;
        readonly List<Dictionary<string, Func<DataRow, string, object>>> _showFields;

        /// <summary>由DataTable数据集导出Excel</summary>
        /// <param name="dt">数据集</param>
        public ExcelHelper(DataTable dt)
        {
            _ds = new DataSet();
            _ds.Tables.Add(dt.Copy());
        }

        /// <summary>由DataTable数据集导出Excel</summary>
        /// <param name="dt">数据集</param>
        /// <param name="showFields">字段名与格式化方法组成.例:showFields.Add("备注,Comments", (dr, field) => dr[field]);</param>
        public ExcelHelper(DataTable dt, Dictionary<string, Func<DataRow, string, object>> showFields)
        {
            _ds = new DataSet();
            _ds.Tables.Add(dt.Copy());
            _showFields = showFields == null ? null : new List<Dictionary<string, Func<DataRow, string, object>>> { showFields };
        }

        /// <summary>由DataSet数据集导出Excel</summary>
        /// <param name="ds">数据集</param>
        /// <param name="showFields">字段名与格式化方法组成,最后一个且表个数加1的字典为共用转换字典</param>
        public ExcelHelper(DataSet ds, List<Dictionary<string, Func<DataRow, string, object>>> showFields)
        {
            _ds = ds;
            _showFields = showFields;
        }

        bool ConvertDataTableColumn(KeyValuePair<string, Func<DataRow, string, object>> showField, int tableIndex)
        {
            if (showField.Value == null) return false;
            string colName = showField.Key.Split(',')[1];
            int colIndex = _ds.Tables[tableIndex].Columns.IndexOf(colName);
            if (colIndex < 0) return false;
            List<object> colValue = new List<object>();
            for (int i = 0; i < _ds.Tables[tableIndex].Rows.Count; i++)
            {
                colValue.Add(showField.Value(_ds.Tables[tableIndex].Rows[i], colName));
            }
            if (!colValue.Any()) return false;
            _ds.Tables[tableIndex].Columns.RemoveAt(colIndex);
            _ds.Tables[tableIndex].Columns.Add(colName, colValue.First().GetType()).SetOrdinal(colIndex);
            for (int i = 0; i < _ds.Tables[tableIndex].Rows.Count; i++)
                _ds.Tables[tableIndex].Rows[i][colIndex] = colValue[i];
            return true;
        }

        /// <summary> 导入Excel</summary>
        /// <param name="strFileName">文件</param>
        /// <returns></returns>
        public static DataTable ReadExcel(string strFileName)
        {
            Workbook book = new Workbook(strFileName);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary> 导入Excel</summary>
        /// <param name="strFileName">文件</param>
        /// <returns></returns>
        public static DataTable ReadExcel(string strFileName, string sheet_num)
        {
            Workbook book = new Workbook(strFileName);
            Worksheet sheet = book.Worksheets[sheet_num];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary> 导入Excel</summary>
        /// <param name="stream">文件流</param>
        /// <returns></returns>
        public static DataTable ReadExcel(Stream stream)
        {
            Workbook book = new Workbook(stream);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary>输出报表</summary>
        /// <param name="filename">输出文件</param>
        /// <param name="startRow">开始行</param>
        /// <param name="templetPath">模板文件</param>
        public void Export(string filename, int startRow = 0, string templetPath = null)
        {
            Export(filename, null, startRow, templetPath);
        }

        /// <summary>输出报表</summary>
        /// <param name="filename">输出文件</param>
        /// <param name="GetFieldNotes">调用实体的GetFieldNotes获取</param>
        /// <param name="startRow">开始行</param>
        /// <param name="templetPath">模板文件</param>
        /// <param name="stream">保存到流中</param>
        public void Export(string filename, Dictionary<string, string> GetFieldNotes, int startRow = 0, string templetPath = null, Stream stream = null)
        {
            bool havePath = false;
            if (!string.IsNullOrWhiteSpace(templetPath))
            {
                if (!Path.IsPathRooted(templetPath)) templetPath = AppDomain.CurrentDomain.BaseDirectory + templetPath;
                havePath = File.Exists(templetPath);
            }
            if (_showFields != null)
                for (int tableIndex = 0; tableIndex < _ds.Tables.Count && tableIndex < _showFields.Count; tableIndex++)
                {
                    if (_showFields[tableIndex] != null)
                        foreach (var showField in _showFields[tableIndex])
                            ConvertDataTableColumn(showField, tableIndex);
                    int showCount = _showFields.Count();
                    if (showCount > _ds.Tables.Count && _showFields[showCount - 1] != null)
                        foreach (var showField in _showFields[showCount - 1])
                            ConvertDataTableColumn(showField, tableIndex);
                }
            Workbook workbook = havePath ? new Workbook(templetPath) : new Workbook();
            for (int tableIndex = 0; tableIndex < _ds.Tables.Count; tableIndex++)
            {
                Worksheet sheet;
                if (tableIndex < workbook.Worksheets.Count)
                {
                    sheet = workbook.Worksheets[tableIndex];
                }
                else
                {
                    sheet = workbook.Worksheets.Add("");
                }
                sheet.Name = _ds.Tables[tableIndex].TableName;

                if (_ds.Tables[tableIndex].Rows.Count > 0)
                {
                    if (havePath)
                        sheet.Cells.ImportDataTable(_ds.Tables[tableIndex], string.IsNullOrWhiteSpace(templetPath)
                       , startRow, 0, true, true);
                    else
                        sheet.Cells.ImportDataTable(_ds.Tables[tableIndex], string.IsNullOrWhiteSpace(templetPath)
                        , startRow, 0, _ds.Tables[tableIndex].Rows.Count, 100, true, "yyyy-MM-DD HH:mm:ss");
                }
                //第一行列名
                if (havePath || _showFields == null) continue;
                if (tableIndex < _showFields.Count)
                    foreach (var showField in _showFields[tableIndex])
                    {
                        string colName = showField.Key.Split(',')[1];
                        int colIndex = _ds.Tables[tableIndex].Columns.IndexOf(colName);
                        sheet.Cells[0, colIndex].Value = showField.Key.Split(',')[0];
                    }
                int showCount = _showFields.Count();
                if (showCount > _ds.Tables.Count && _showFields[showCount - 1] != null)
                    foreach (var showField in _showFields[showCount - 1])
                    {
                        string colName = showField.Key.Split(',')[1];
                        int colIndex = _ds.Tables[tableIndex].Columns.IndexOf(colName);
                        sheet.Cells[0, colIndex].Value = showField.Key.Split(',')[0];
                    }
                //从元信息里查找
                if (GetFieldNotes == null) continue;
                for (int i = 0; i < _ds.Tables[tableIndex].Columns.Count; i++)
                {
                    string colNote;
                    if (GetFieldNotes.TryGetValue(sheet.Cells[0, i].Value.ToString(), out colNote))
                        sheet.Cells[0, i].Value = colNote;
                }
            }
            if (stream == null)
            {
                if (HttpContext.Current == null) return;
                HttpContext.Current.Response.Clear();
                workbook.Save(HttpContext.Current.Response, HttpUtility.UrlEncode(filename, Encoding.UTF8)
                              , ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
            }
            else
            {
                workbook.Save(stream, SaveFormat.Xlsx);
            }
        }

    }
}
